﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using NHibernate;
using NHibernate.Criterion;
using Rodunu.Movies.Contracts;
using Rodunu.Movies.Data;
using Rodunu.Movies.Interfaces.Movie;
using System.Linq;

namespace Rodunu.Movies.Logic.Movie
{
    public class UserMoviesLogic : Repository<Contracts.UserMovies>, IUserMovies
    {
        #region IUserMovies Members

        public IList<UserMovies> GetMoviesByUser(long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IList<UserMovies> movies = session.CreateCriteria(typeof(Contracts.UserMovies))
                                                     .Add(Expression.Eq("UserInfo.Id", userId))
                                                     .List<UserMovies>();
            //DBHelper.CloseSession();
            return movies.OrderBy(o => o.Name).ToList();
        }

        public IList<UserMovies> GetMovieChildren(long parentId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IList<UserMovies> movies = session.CreateCriteria(typeof(Contracts.UserMovies))
                                                     .Add(Expression.Eq("ParentId", parentId))
                                                     .List<UserMovies>();
            //DBHelper.CloseSession();
            return movies.OrderBy(o => o.Name).ToList();
        }

        public IList<UserMovies> GetMoviesByUSerAdvanced(long userId, long? collectionId, int? mediaTypeId, bool? viewed, int? userMoviesStatusId, int? conditionId)
        {
            ISession session = DBHelper.GetCurrentSession();

            ICriteria movieCriteria = session.CreateCriteria(typeof(Contracts.UserMovies));
            movieCriteria.Add(Expression.Eq("UserInfo.Id", userId));
            movieCriteria.Add(Expression.IsNull("ParentId"));

            if (collectionId != null)
                movieCriteria.Add(Expression.Eq("UserCollections.Id", collectionId));
            if (mediaTypeId != null)
                movieCriteria.Add(Expression.Eq("MovieTypes.Id", mediaTypeId));
            if (viewed != null)
                movieCriteria.Add(Expression.Eq("Viewed", viewed));
            if (userMoviesStatusId != null)
                movieCriteria.Add(Expression.Eq("UserMoviesStatus.Id", userMoviesStatusId));
            if (conditionId != null)
                movieCriteria.Add(Expression.Eq("Condition.Id", conditionId));

            return movieCriteria.List<UserMovies>().OrderBy(o => o.Name).ToList();
        }

        public IList<UserMovies> SearchForMoviesByUser(long userId, string movie)
        {
            return SearchForMoviesByProc("SearchFriendsMovies", movie, userId);
        }

        public IList<UserMovies> SearchForMovies(long userId, string movie)
        {
            return SearchForMoviesByProc("SearchForMovies", movie, userId);
        }

        public UserMovies OwnMovie(long userId, long movieId)
        {
            ISession session = DBHelper.GetCurrentSession();
            var movies = session.CreateCriteria(typeof (Contracts.UserMovies))
                                                   .Add(Expression.Eq("UserInfo.Id", userId))
                                                   .Add(Expression.Eq("Movie.Id", movieId))
                                                   .List<Contracts.UserMovies>();
            //DBHelper.CloseSession();
            if (movies == null || movies.Count == 0)
                return new UserMovies();
            return movies.First();
        }

        public IList<UserMovies> GetMoviesByLetters(string letters, long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IList<UserMovies> movies = session.CreateCriteria(typeof(UserMovies))
                                                     .Add(Expression.Like("Name", "%" + letters + "%"))
                                                     .Add(Expression.Eq("UserInfo.Id", userId))
                                                     .SetMaxResults(20)
                                                     .List<UserMovies>();
            //DBHelper.CloseSession();
            return movies;
        }

        public void LinkMovieToUserMovie(long movieId, long userMovieId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IQuery query = session.GetNamedQuery("LinkMovieToUserMovie");
            query.SetInt64("MovieId", movieId);
            query.SetInt64("UserMovieId", userMovieId);
            query.UniqueResult();
        }

        public IList<UserMovies> GetLinkMovies(long movieId, long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IList<UserMovies> movies = session.CreateCriteria(typeof(UserMovies))
                                                     .Add(Expression.Eq("UserInfo.Id", userId))
                                                     .Add(Expression.Eq("Movie.Id", movieId))
                                                     .List<UserMovies>().OrderBy(o => o.Name).ToList();
            //DBHelper.CloseSession();
            return movies.OrderBy(o => o.Name).ToList();
        }

        public IList<UserMovies> GetFriendsUserMoviesForMovies(long movieId, long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IQuery query = session.GetNamedQuery("GetFriendsUserMoviesForMovies");
            query.SetInt64("MovieId", movieId);
            query.SetInt64("UserId", userId);
            return query.List<UserMovies>();
        }

        public IList<UserMovies> GetFavorites(long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IList<UserMovies> movies = session.CreateCriteria(typeof(UserMovies))
                                                     .Add(Expression.Eq("UserInfo.Id", userId))
                                                     .Add(Expression.Eq("IsFavorite", true))
                                                     .List<UserMovies>().OrderBy(o => o.Name).ToList();
            //DBHelper.CloseSession();
            return movies.OrderBy(o => o.Name).ToList();
        }

        public IList<UserMovies> GetMoviesByFirstLetter(string letter, long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            ICriteria criteria = session.CreateCriteria(typeof (UserMovies))
                .Add(Expression.Eq("UserInfo.Id", userId))
                .Add(Expression.IsNull("ParentId"));

            if (letter == "#")
                criteria.Add(Expression.Like("Name", @"[.*!@#$^(),{}/\0123456789]%"));
            else
                criteria.Add(Expression.Like("Name", letter + "%"));
            return criteria.List<UserMovies>().OrderBy(o => o.Name).ToList();
        }

        private static IList<UserMovies> SearchForMoviesByProc(string proc, string movie, long userId)
        {
            ISession session = DBHelper.GetCurrentSession();
            IQuery query = session.GetNamedQuery(proc);
            query.SetString("MovieName", @"""*" + movie + @"*""");
            query.SetInt64("UserId", userId);
            return query.List<UserMovies>();
        }

        public IList<UserMovies> SearchForMovies(string movie, long userId)
        {
            if (string.IsNullOrEmpty(movie))
                return new List<UserMovies>();
            movie = movie.Replace("'", ".");
            string[] keywords = movie.Split('\u0020');
            string likeClause = string.Empty;

            foreach (string s in keywords)
            {
                if (!string.IsNullOrEmpty(s) && MovieSearchHelper.CheckIfKeyword(s))
                {
                    var stringSearch = s.Trim();
                    stringSearch = stringSearch.Replace("'", "''");
                    if (MovieSearchHelper.IsNumber(stringSearch))
                        stringSearch += "*";
                    likeClause += @" CONTAINS([Name],'""" + stringSearch + @"""') AND";
                }
            }

            if (string.IsNullOrEmpty(likeClause))
                return new List<UserMovies>();

            likeClause = likeClause.Substring(0, likeClause.Length - 4);
            likeClause += @" OR CONTAINS([Name],'""" + movie + @"""')";
            string sqlCommand = "SELECT {UserMovies.*} " +
                                "FROM UserMovies {UserMovies} WHERE UserId = " + userId + " AND (" + likeClause + ")";
            ISession session = DBHelper.GetCurrentSession();
            return session.CreateSQLQuery(sqlCommand, "UserMovies", typeof(UserMovies)).List<UserMovies>();
        }

        public IList<UserMovies> SearchForFriendsMovies(string movie, long userId)
        {
            if (string.IsNullOrEmpty(movie))
                return new List<UserMovies>();
            movie = movie.Replace("'", ".");
            string[] keywords = movie.Split('\u0020');
            string likeClause = string.Empty;

            foreach (string s in keywords)
            {
                if (!string.IsNullOrEmpty(s) && MovieSearchHelper.CheckIfKeyword(s))
                {
                    var stringSearch = s.Trim();
                    stringSearch = stringSearch.Replace("'", "''");
                    if (MovieSearchHelper.IsNumber(stringSearch))
                        stringSearch += "*";
                    likeClause += @" CONTAINS(UserMovies.[Name],'""" + stringSearch + @"""') AND";
                }
            }

            if (string.IsNullOrEmpty(likeClause))
                return new List<UserMovies>();

            likeClause = likeClause.Substring(0, likeClause.Length - 4);
            likeClause += @" OR CONTAINS(UserMovies.[Name],'""" + movie + @"""')";
            string sqlCommand = "SELECT UserMovies.*, Movie.Id " +
                                "FROM Friends Friends " +
                                "LEFT JOIN UserMovies on UserMovies.UserId = Friends.FriendId " +
                                "LEFT JOIN Movie on UserMovies.MovieId = Movie.Id " +
                                "WHERE Friends.Block = 0 AND Friends.DeleteFriend = 0 AND Friends.Pending = 0 " +
                                "AND (" + likeClause + ") " +
                                "AND Friends.UserId = " + userId + " " +
                                "AND Friends.FriendId != " + userId + " " +
                                "AND UserMovies.UserMoviesStausId <> 5";

            ISession session = DBHelper.GetCurrentSession();
            return session.CreateSQLQuery(sqlCommand, "UserMovies", typeof(UserMovies)).List<UserMovies>();
        }

        public int GetCollectionsForUser(long userId)
        {
            string sqlCommand = "select count(distinct(id)) from usermovies where userid = " + userId + " and iscollection = 1";
            ISession session = DBHelper.GetCurrentSession();
            return (int) session.CreateSQLQuery(sqlCommand).UniqueResult();
        }

        public int GetTotalMovies(long userId)
        {
            string sqlCommand = "select count(distinct(id)) from usermovies where userid = " + userId + " and (iscollection = 1 or parentid is null)";
            ISession session = DBHelper.GetCurrentSession();
            return (int)session.CreateSQLQuery(sqlCommand).UniqueResult();
        }

        public int GetDiscs(long userId)
        {
            string sqlCommand = "select count(distinct(id)) from usermovies where userid = " + userId;
            ISession session = DBHelper.GetCurrentSession();
            return (int)session.CreateSQLQuery(sqlCommand).UniqueResult();
        }

        public int GetBorrowed(long userId)
        {
            string sqlCommand = "select count(*) from FriendMovieRequest where friendid = " + userId + " and approved = 1 and returndate is null";
            ISession session = DBHelper.GetCurrentSession();
            return (int)session.CreateSQLQuery(sqlCommand).UniqueResult();
        }

        public IList<UserMovies> GetBorrowedMovies(long userId)
        {
            string sqlCommand = "SELECT um.*  FROM [RMovies].[dbo].[FriendMovieRequest] fmr left join usermovies um on um.id = fmr.usermovieid where friendid = " + userId + " and approved = 1 and returndate is null";
            ISession session = DBHelper.GetCurrentSession();
            return session.CreateSQLQuery(sqlCommand, "UserMovies", typeof(UserMovies)).List<UserMovies>().OrderBy(o => o.UserInfo.Id).ToList();
        }

        public IList<UserMovies> RecommendUnwatched(long userId)
        {
            string sqlCommand = "SELECT top 1 * FROM UserMovies where userid = " + userId + " and parentid is null and UserMoviesStausId = 1 ORDER BY NEWID()";
            ISession session = DBHelper.GetCurrentSession();
            return session.CreateSQLQuery(sqlCommand, "UserMovies", typeof(UserMovies)).List<UserMovies>().OrderBy(o => o.UserInfo.Id).ToList();
        }
        
        #endregion
    }
}
